/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.ZoneDao;
import cc.altius.model.AppZone;
import cc.altius.model.City;
import cc.altius.model.MasterZone;
import cc.altius.model.Zone;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author santosh
 */
@Repository
public class ZoneDaoImpl implements ZoneDao {

    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    @Autowired
    private DataSource dataSource;
    Session session;
    Query query;


    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    @Transactional
    public List<City> getCityList() {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("City.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public int addMasterZone(MasterZone masterZone) {
       return (Integer) this.hibernateTemplate.save(masterZone);
    }

    @Override
    @Transactional
    public List<MasterZone> getMasterZoneList() {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("MasterZone.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public int updateMasterZone(MasterZone masterZone) {

        String hql = "update MasterZone set masterZoneCode=:masterZoneCode, cityId.cityId=:cityId"
                + " where masterZoneId=:masterZoneId";
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery(hql);
        query.setParameter("masterZoneCode", masterZone.getMasterZoneCode());
        query.setParameter("cityId", masterZone.getCityId().getCityId());
        query.setParameter("masterZoneId", masterZone.getMasterZoneId());
        int result = query.executeUpdate();
        return result;
    }

    @Override
    @Transactional
    public MasterZone getMasterZoneById(int masterZoneId) {
        Session session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(MasterZone.class);
        criteria.add(Restrictions.eq("masterZoneId", masterZoneId));
        return (MasterZone) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public int addZone(Zone zone) {
        return  (Integer) this.hibernateTemplate.save(zone);
    }

    @Override
    @Transactional
    public List<Zone> getZoneList() {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.getNamedQuery("Zone.findAll");
        return query.list();
    }

    @Override
    @Transactional
    public Zone getZoneById(int zoneId) {
        Session session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(Zone.class);
        criteria.add(Restrictions.eq("zoneId", zoneId));
        return (Zone) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public int updateZone(Zone zone) {
        System.out.println("update zone=" + zone.getZoneId());
        String hql = "update "
                + "Zone "
                + "set "
                + "zoneCode=:zoneCode, "
                + "zoneName=:zoneName, "
                + "zonePriority=:zonePriority,"
                + "masterZoneId.masterZoneId=:masterZoneId "
                + "where zoneId=:zoneId";
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery(hql);
        query.setParameter("zoneCode", zone.getZoneCode());
        query.setParameter("zoneName", zone.getZoneName());
        query.setParameter("zonePriority", zone.getZonePriority());
        query.setParameter("masterZoneId", zone.getMasterZoneId().getMasterZoneId());
        query.setParameter("zoneId", zone.getZoneId());

        int result = query.executeUpdate();
        return result;
    }

    @Override
    @Transactional
    public List<Zone> listZoneForAutocomplete(String zoneName) {
        return this.hibernateTemplate.find("from Zone zone WHERE zone.zoneName like '" + zoneName + "%'");
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterMasterZone(int stateId, int cityId) {
        String sql = "SELECT master_zone.MASTER_ZONE_ID, master_zone.MASTER_ZONE_CODE, city.CITY_NAME, state.STATE_NAME FROM master_zone LEFT JOIN city on master_zone.CITY_ID=city.CITY_ID LEFT JOIN state on city.STATE_ID=state.STATE_ID WHERE 1 ";
        if (cityId!=0) {
            sql +=  " AND master_zone.CITY_ID=" + cityId;
        }
        if (stateId!=0) {
            sql += " AND city.STATE_ID=" + stateId;
        }
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map; 
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterZone(int stateId, int cityId, int masterZoneId) {
        
        String sql = "SELECT zone.ZONE_ID,zone.ZONE_CODE, zone.ZONE_NAME, master_zone.MASTER_ZONE_CODE, city.CITY_NAME, state.STATE_NAME "
                + "FROM zone LEFT JOIN master_zone on zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID LEFT JOIN city on master_zone.CITY_ID=city.CITY_ID LEFT JOIN state on city.STATE_ID=state.STATE_ID WHERE 1 ";
        if(masterZoneId!=0)
        {
            sql +=  " AND zone.MASTER_ZONE_ID=" + masterZoneId;
        }
        if (cityId!=0) {
            sql +=  " AND master_zone.CITY_ID=" + cityId;
        }
        if (stateId!=0) {
            sql += " AND city.STATE_ID=" + stateId;
        }
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map; 
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional
    public Zone getZoneByZoneName(String zoneName,String zoneCode) {
        String hqlQuery = "from Zone zone  WHERE zone.zoneName=:zoneName OR zone.zoneCode=:zoneCode";
        
        try {
            session=this.hibernateTemplate.getSessionFactory().getCurrentSession();
            query = session.createQuery(hqlQuery).setParameter("zoneName", zoneName).setParameter("zoneCode", zoneCode);
            return (Zone) query.uniqueResult();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    @Transactional
    public MasterZone getMasterZoneByMasterZoneCode(String masterZoneCode) {
        String hqlQuery = "from MasterZone mz  WHERE mz.masterZoneCode=:masterZoneCode";
        
        try {
            session=this.hibernateTemplate.getSessionFactory().getCurrentSession();
            query = session.createQuery(hqlQuery).setParameter("masterZoneCode", masterZoneCode);
            return (MasterZone) query.uniqueResult();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<AppZone> getAppZoneList() {
        List<AppZone> appZone = this.hibernateTemplate.find("from AppZone");
        return appZone;
    }
}
